﻿using API_lib.Util;
using System;
using System.Collections.Generic;
using System.Text;

namespace API_lib.Persistence.SQL
{
    /// <summary>
    /// Creates the SQL statements
    /// </summary>
    public class SQLQueries
    {
	    private SQLQueries() { }

        /// <summary>
        /// Returns the given value single-quoted as used in an SQL query.
        /// </summary>
        /// <param name="value">the value to be quoted</param>
        /// <returns>the quoted value</returns>
	    public static String getFieldValueQuoted(Object value) 
        {
		    return getFieldValue(value, true);
	    }

        /// <summary>
        /// Returns the given value non-quoted. (Meaningful for number type fields and if value is NULL.)
        /// </summary>
        /// <param name="value">the value</param>
        /// <returns>the formatted value</returns>
	    public static String getFieldValueUnquoted(Object value) 
        {
		    return getFieldValue(value, false);
	    }

        /// <summary>
        /// Returns the Y or N value based on the given parameter.
        /// </summary>
        /// <param name="value">the boolean value to format</param>
        /// <returns>the formatted boolean value</returns>
	    public static String getBooleanValueYesNo(Boolean value)
        {
		    return getFieldValueQuoted(value ? SQLConstants.FIELD_VALUE_BOOLEAN_YES : SQLConstants.FIELD_VALUE_BOOLEAN_NO);
	    }

        /// <summary>
        /// Returns the 1 or 0 value based on the given parameter.
        /// </summary>
        /// <param name="value">the boolean value to format</param>
        /// <returns>the formatted boolean value</returns>
	    public static String getBooleanValueTrueFalse(Boolean value) 
        {
		    return getFieldValueUnquoted(value ? SQLConstants.FIELD_VALUE_BOOLEAN_TRUE : SQLConstants.FIELD_VALUE_BOOLEAN_FALSE);
	    }

        /// <summary>
        /// Creates a select query returning all fields in the table.
        /// 
        /// WhereClause and order by should be null.
        /// </summary>
        /// <param name="tableName">the name of the database table</param>
        /// <param name="whereClause">the where clause object for the query</param>
        /// <param name="orderByClause">the order by clause for the query</param>
        /// <returns>the "select * from tableName where ... order by ..." SQL query</returns>
        public static String createSelectQueryAllFields(String tableName, String whereClause, String orderByClause) 
	    {
		    return createSelectQuery(tableName, false, SQLConstants.SYMBOL_ALL_FIELDS, whereClause, orderByClause);
	    }

        /// <summary>
        /// Creates a select query returning the specified fields in the table with given
	    /// where and order by clauses.
        /// </summary>
        /// <param name="tableName">the name of the database table</param>
        /// <param name="fields">the array of the fields to return in the resultset</param>
        /// <param name="whereClause">the where clause object for the query</param>
        /// <param name="orderByClause">the order by clause for the query</param>
        /// <returns>the "select field1, field2, ... where ... order by ..." SQL query</returns>
	    public static String createSelectQuery(String tableName, String[] fields, String whereClause, String orderByClause) 
        {
            return createSelectQuery(tableName, new List<String>((String[])fields), whereClause, orderByClause);
	    }

        /// <summary>
        /// Creates a select query returning the specified fields in the table with given
        /// where and order by clauses.
        /// </summary>
        /// <param name="tableName">the name of the database table</param>
        /// <param name="fields">the array of the fields to return in the resultset</param>
        /// <param name="whereClause">the where clause object for the query</param>
        /// <param name="orderByClause">the order by clause for the query</param>
        /// <returns>the "select field1, field2, ... where ... order by ..." SQL query</returns>
	    public static String createSelectQuery(String tableName, List<String> fields, String whereClause, String orderByClause) 
        {
		    String separator = new StringBuilder(2).Append(SQLConstants.SYMBOL_SEPARATOR_FIELD).Append(SQLConstants.SYMBOL_SPACE).ToString();
		    String fieldsString = DataFormat.join(fields, separator);

		    return createSelectQuery(tableName, false, fieldsString, whereClause.ToString(), orderByClause);
	    }

        /// <summary>
        /// Creates an insert query with the given field names and values.
	    /// The Length of fields and values must be the same!
        /// </summary>
        /// <param name="tableName">the name of the database table</param>
        /// <param name="fields">the array of the fields to return in the resultset</param>
        /// <param name="values">the array of the field values</param>
        /// <returns>the "insert into table (field1, field2, ...) values (value1, value2, ...)" SQL query</returns>
	    public static String createInsertQuery(String tableName, String[] fields, String[] values) 
        {
            return createInsertQuery(tableName, new List<String>((String[])fields), new List<String>((String[])values));
	    }

        /// <summary>
        /// Creates an insert query with the given field names and values.
        /// The Length of fields and values must be the same!
        /// </summary>
        /// <param name="tableName">the name of the database table</param>
        /// <param name="fields">the array of the fields to return in the resultset</param>
        /// <param name="values">the array of the field values</param>
        /// <returns>the "insert into table (field1, field2, ...) values (value1, value2, ...)" SQL query</returns>
	    public static String createInsertQuery(String tableName, List<String> fields, List<String> values) 
        {
		    String query;
		    // the size of fields and values must be the same, this is only a safety check
		    if (fields.Count == values.Count) 
            {
			    String separator = new StringBuilder(2).Append(SQLConstants.SYMBOL_SEPARATOR_FIELD).Append(SQLConstants.SYMBOL_SPACE).ToString();
			    String fieldsString = DataFormat.join(fields, separator);
			    String valuesString = DataFormat.join(values, separator);
			    query = createInsertQuery(tableName, fieldsString, valuesString);
		    } 
            else 
            {
			    query = "";
		    }

		    return query;
	    }

        /// <summary>
        /// Creates an <code>update</code> query for the given fields and values with the given
	    /// where clause.
	    /// The Length of <code>fields</code> and <code>values</code> must be the same!
        /// </summary>
        /// <param name="tableName">the name of the database table</param>
        /// <param name="fields">the array of the fields to return in the resultset</param>
        /// <param name="values">the array of the field values</param>
        /// <param name="whereClause">the where clause for the query</param>
        /// <returns>the "update table set field1 = value1, field2 = value2, ... where ..." SQL query</returns>
	    public static String createUpdateQuery(String tableName, String[] fields, String[] values, String whereClause) 
        {
            return createUpdateQuery(tableName, new List<String>((String[])fields), new List<String>((String[])values), whereClause);
	    }

        /// <summary>
        /// Creates an <code>update</code> query for the given fields and values with the given
        /// where clause.
        /// The Length of <code>fields</code> and <code>values</code> must be the same!
        /// </summary>
        /// <param name="tableName">the name of the database table</param>
        /// <param name="fields">the array of the fields to return in the resultset</param>
        /// <param name="values">the array of the field values</param>
        /// <param name="whereClause">the where clause for the query</param>
        /// <returns>the "update table set field1 = value1, field2 = value2, ... where ..." SQL query</returns>
	    public static String createUpdateQuery(String tableName, List<String> fields, List<String> values, String whereClause) 
        {
		    StringBuilder result = new StringBuilder(1000);
            if (fields.Count == values.Count && !(fields.Count==0))
            {
			    result.Append(SQLConstants.KEYWORD_UPDATE).Append(getWithSpace(tableName)).Append(getWithSpace(SQLConstants.KEYWORD_SET));
                for (int i = 0; i < fields.Count; i++)
                {
				    if (i > 0) {
					    result.Append(SQLConstants.SYMBOL_SEPARATOR_FIELD);
				    }
				    result.Append(getWithSpace(fields[i])).Append(getWithSpace(SQLConstants.SYMBOL_EQUALS)).Append(getWithSpace(values[i]));
			    }
			    if (whereClause!="") {
				    result.Append(getWithSpace(SQLConstants.KEYWORD_WHERE)).Append(getWithSpace(whereClause));
			    }
		    }
		    return result.ToString();
	    }

        /// <summary>
        /// Creates a <code>delete</code> query with the given where clause.
        /// </summary>
        /// <param name="tableName">the name of the database table</param>
        /// <param name="whereClause">the where clause object for the query</param>
        /// <returns>the "delete from table where ..." SQL query</returns>
	    public static String createDeleteQuery(String tableName, String whereClause) 
        {
		    StringBuilder result = new StringBuilder(200);
		    result.Append(SQLConstants.KEYWORD_DELETE).Append(getWithSpace(SQLConstants.KEYWORD_FROM)).Append(getWithSpace(tableName));

		    if (whereClause!="")
            {
			    result.Append(getWithSpace(SQLConstants.KEYWORD_WHERE)).Append(getWithSpace(whereClause));
		    }
		    return result.ToString();
	    }

        /// <summary>
        /// Creates  CREATE query
        /// </summary>
        /// <param name="tableName">name of the table to be created</param>
        /// <param name="fields">the fields of the table, FORMATTED!</param>
        /// <returns>returns the CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z); SQL query</returns>
        public static String createCreateQuery(String tableName, String fields)
        {
            StringBuilder result = new StringBuilder(1000);

            result.Append(SQLConstants.KEYWORD_CREATE).Append(getWithSpace(SQLConstants.KEYWORD_TABLE)).Append(getWithSpace(tableName));
            result.Append(SQLConstants.SYMBOL_OPEN_PARENTHESES).Append(fields).Append(SQLConstants.SYMBOL_CLOSE_PARENTHESES);

            return result.ToString();
        }

        /// <summary>
        /// Returns the proper order direction represented by the given parameter.
	    /// Only ASC (this is the default result) or DESC is returned.
        /// </summary>
        /// <param name="direction">the direction</param>
        /// <returns>the proper order direction - if parameter is invalid or empty or <code>null</code>, <code>ASC</code> will be returned.</returns>
	    public static String getOrderDirection(String direction) 
        {
		    String result = SQLConstants.ORDER_ASCENDING;

		    if (direction != null && direction.ToLower().StartsWith(SQLConstants.ORDER_DESCENDING)) 
            {
			    result = SQLConstants.ORDER_DESCENDING;
		    }
		    return result;
	    }

        /// <summary>
        /// Returns the properly formatted field value for an SQL query.
        /// </summary>
        /// <param name="value">the (preformatted) value</param>
        /// <param name="quoted">tells whether quotes are required</param>
        /// <returns>the fully formatted field value</returns>
	    public static String getFieldValue(Object value, Boolean quoted) 
        {
		    StringBuilder result = new StringBuilder();
		    String valueString = Convert.ToString(value);

            if (value == null || SQLConstants.FIELD_VALUE_NULL.Equals(valueString, StringComparison.CurrentCultureIgnoreCase))
            {
			    result.Append(SQLConstants.FIELD_VALUE_NULL);
		    } 
            else 
            {
			    result.Append(quoted ? SQLConstants.SYMBOL_QUOTE : "");
			    result.Append(valueString.Trim());
			    result.Append(quoted ? SQLConstants.SYMBOL_QUOTE : "");
		    }
		    return result.ToString();
	    }

        /// <summary>
        /// Returns the list of values quoted for the SQL queries.
        /// </summary>
        /// <param name="values">values the list containing the values to be quoted</param>
        /// <returns>the list of quoted values</returns>
	    public static List<String> getListQuoted(List<Object> values) 
        {
		    List<String> result = new List<String>(values == null ? 0 : values.Count);

		    if (values != null && values.Count > 0) 
            {
			    foreach (Object item in values) 
                {
				    result.Add(getFieldValueQuoted(Convert.ToString(item)));
			    }
		    }
		    return result;
	    }

	    // PRIVATE HELPER METHODS 

        /// <summary>
        /// Private helper method for creating select queries.
        /// </summary>
	    private static String createSelectQuery(String tableName, Boolean distinct, String fields, String whereClause, String orderByClause) 
	    {
		    StringBuilder result = new StringBuilder(1000);
		    result.Append(SQLConstants.KEYWORD_SELECT);

		    if (distinct) {
			    result.Append(getWithSpace(SQLConstants.KEYWORD_DISTINCT));
		    }

		    result.Append(getWithSpace(fields)).Append(getWithSpace(SQLConstants.KEYWORD_FROM));
		    result.Append(getWithSpace(tableName));

		    if ((whereClause != null) && (whereClause != "")) {
			    result.Append(getWithSpace(SQLConstants.KEYWORD_WHERE)).Append(getWithSpace(whereClause));
		    }

		    if ((orderByClause != null) && (orderByClause != "")) {
			    result.Append(getWithSpace(orderByClause));
		    }

		    return result.ToString();
	    }

        /// <summary>
        /// Private helper method for creating insert queries.
        /// </summary>
	    private static String createInsertQuery(String tableName, String fields, String values) 
        {
		    StringBuilder result = new StringBuilder(tableName.Length + fields.Length + values.Length + 50);

		    result.Append(SQLConstants.KEYWORD_INSERT).Append(getWithSpace(SQLConstants.KEYWORD_INTO)).Append(getWithSpace(tableName));
		    result.Append(getWithSpace(SQLConstants.SYMBOL_OPEN_PARENTHESES)).Append(fields).Append(SQLConstants.SYMBOL_CLOSE_PARENTHESES);
		    result.Append(getWithSpace(SQLConstants.KEYWORD_VALUES));
		    result.Append(getWithSpace(SQLConstants.SYMBOL_OPEN_PARENTHESES)).Append(values).Append(SQLConstants.SYMBOL_CLOSE_PARENTHESES);

		    return result.ToString();
	    }

        /// <summary>
        /// Private helper method for adding a space character as prefix to given parameter string.
        /// </summary>
        /// <param name="str">the string value to prefix with a space</param>
        /// <returns>the prefixed string</returns>
	    private static String getWithSpace(String str) 
        {
		    return str == null || str.Trim().Length < 1 ? "" : new StringBuilder(str.Length).Append(SQLConstants.SYMBOL_SPACE).Append(str.Trim()).ToString();
	    }
    }
}
